# select testFormNumber as '委托单号',sampleModel as '样品型号',sampleNumber as '样品编号',testFormItemName as '委托项名称',
# equipmentName as '设备名称',equipmentNumber as '设备编号',equipmentTWName as '设备台位名称',testCondition as '检测条件',
# varStr1 as '电流',varStr3 as '排气压力',varStr5 as '吸气压力',varStr7 as '排气温度',varStr9 as '吸气温度',varStr11 as '壳体温度',varStr13 as '频率'
# from actualtestvalueg3 where equipmentNumber = 'TA2211080005160024010101' and testFormNumber = 'ST2024099386' and sampleNumber = '2409060741K';

import os
import tkinter as tk
from tkinter import messagebox

class SqlQueryBuilder:
    def __init__(self, tbl, cons, fields=None):
        self.tbl = tbl
        self.cons = cons
        # 如果 fields 为 None，则使用示例字段列表
        if fields is None:
            fields = [
                "testFormNumber as '委托单号'",
                "sampleModel as '样品型号'",
                "sampleNumber as '样品编号'",
                "testFormItemName as '委托项名称'",
                "equipmentName as '设备名称'",
                "equipmentNumber as '设备编号'",
                "equipmentTWName as '设备台位名称'",
                "testCondition as '检测条件'",
                "varStr1 as '电流'",
                "varStr3 as '排气压力'",
                "varStr5 as '吸气压力'",
                "varStr7 as '排气温度'",
                "varStr9 as '吸气温度'",
                "varStr11 as '壳体温度'",
                "varStr13 as '频率'"
            ]
        self.fields = fields

    def construct_sql(self):
        # 构造 SELECT 部分
        select_part = ", ".join(self.fields)
        
        # 构造 WHERE 条件
        conditions = []
        for key, value in self.cons.items():
            conditions.append(f"{key} = '{value}'")
        
        # 拼接 SQL 查询
        sql = f"SELECT {select_part} FROM {self.tbl} WHERE {' AND '.join(conditions)};"
        return sql

    def write_to_file(self, file_path):
        sql_query = self.construct_sql()
        with open(file_path, 'a') as file:
            file.write(sql_query + '\n')
        print(f"SQL 语句已成功写入到 {file_path}")

def create_gui():
    def generate_sql():
        tbl = table_name_entry.get()
        cons = {}
        for i in range(len(condition_keys_entries)):
            key = condition_keys_entries[i].get()
            value = condition_values_entries[i].get()
            if key and value:
                cons[key] = value
        
        fields = [field_entry.get() for field_entry in fields_entries if field_entry.get()]
        
        builder = SqlQueryBuilder(tbl, cons, fields)
        sql_query = builder.construct_sql()
        result_text.delete(1.0, tk.END)
        result_text.insert(tk.END, sql_query)
    
    def save_sql():
        sql_query = result_text.get(1.0, tk.END).strip()
        if not sql_query:
            messagebox.showwarning("警告", "没有生成的 SQL 语句")
            return
        
        current_directory = os.path.dirname(os.path.abspath(__file__))
        file_path = os.path.join(current_directory, 'sql_query.sql')
        with open(file_path, 'a') as file:
            file.write(sql_query + '\n')
        messagebox.showinfo("成功", f"SQL 语句已成功写入到 {file_path}")

    root = tk.Tk()
    root.title("SQL Query Builder")

    # 表名输入
    tk.Label(root, text="表名:").grid(row=0, column=0, padx=5, pady=5)
    table_name_entry = tk.Entry(root)
    table_name_entry.grid(row=0, column=1, padx=5, pady=5)

    # 查询条件输入
    tk.Label(root, text="查询条件:").grid(row=1, column=0, padx=5, pady=5)
    condition_keys_entries = []
    condition_values_entries = []
    for i in range(3):  # 假设最多支持3个条件
        tk.Label(root, text=f"键{i+1}:").grid(row=2+i, column=0, padx=5, pady=5)
        key_entry = tk.Entry(root)
        key_entry.grid(row=2+i, column=1, padx=5, pady=5)
        condition_keys_entries.append(key_entry)

        tk.Label(root, text=f"值{i+1}:").grid(row=2+i, column=2, padx=5, pady=5)
        value_entry = tk.Entry(root)
        value_entry.grid(row=2+i, column=3, padx=5, pady=5)
        condition_values_entries.append(value_entry)

    # 字段输入
    tk.Label(root, text="字段:").grid(row=5, column=0, padx=5, pady=5)
    fields_entries = []
    for i in range(5):  # 假设最多支持5个字段
        field_entry = tk.Entry(root)
        field_entry.grid(row=6+i, column=1, padx=5, pady=5)
        fields_entries.append(field_entry)

    # 生成 SQL 按钮
    generate_button = tk.Button(root, text="生成 SQL", command=generate_sql)
    generate_button.grid(row=11, column=0, columnspan=2, padx=5, pady=5)

    # 保存 SQL 按钮
    save_button = tk.Button(root, text="保存 SQL", command=save_sql)
    save_button.grid(row=11, column=2, columnspan=2, padx=5, pady=5)

    # 结果显示
    tk.Label(root, text="生成的 SQL 语句:").grid(row=12, column=0, padx=5, pady=5)
    result_text = tk.Text(root, height=10, width=80)
    result_text.grid(row=13, column=0, columnspan=4, padx=5, pady=5)

    root.mainloop()

if __name__ == "__main__":
    create_gui()